package com.googlecode.groovy_toy_orm

import com.googlecode.groovy_toy_orm.framework.BaseFunctionalTest
import groovy.sql.Sql
import net.sourceforge.jtds.jdbcx.JtdsDataSource

import java.text.SimpleDateFormat


/**
 * @author: DavydovMD
 * Date: 02.04.13
 * Time: 15:05
 */

//
//enum ConditionOp {
//    EQ('='), LT('<'), GT('>'), LTE('<='), GTE('>='), IN('in'), NOT_NULL('is not null'), IS_NULL('is null')
//    def sqlOp
//
//    ConditionOp(sqlOp) {
//        this.sqlOp = sqlOp
//    }
//
//
//}

class SqlMappingUtil {
    /*  MSSQL
        update tablename set datetimefield='19980223 14:23:05'
        update tablename set datetimefield='02/23/1998 14:23:05'
        update tablename set datetimefield='1998-12-23 14:23:05'
        update tablename set datetimefield='23 February 1998 14:23:05'
        update tablename set datetimefield='1998-02-23T14:23:05'*/
    final static SimpleDateFormat msSqlDateFmt = new SimpleDateFormat('yyyy-MM-dd HH:mm:ss.SSS')
    final static SimpleDateFormat sqLiteDateFmt = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss.SSS")


    public static String getDbUrl(Sql db) {
        db.getConnection().getMetaData().getURL()
    }

    public static String dateToSql(Sql db, Date date) {
        if (db.dataSource instanceof JtdsDataSource) {
            "'${msSqlDateFmt.format(date)}'"
        } else {
            "'${sqLiteDateFmt.format(date)}'"
        }
    }

    public static String dateToSql(Date date) {
        "'${msSqlDateFmt.format(date)}'"
    }

    private static quoteAsNeeded(Sql db, Object value) {
        if (value instanceof String) {
            "'${value}'"// quote
        } else if (value instanceof Date) {
            dateToSql(db, value)
        } else {
            value
        }
    }

    private static quoteAsNeeded(Object value) {
        if (value instanceof String) {
            "'${value}'"// quote
        } else if (value instanceof Date) {
            dateToSql(value)
        } else {
            value
        }
    }

    public static String buildInClause(ConditionOp op, List values) {
        List<String> quotedVals = []
        values.each {
            quotedVals << quoteAsNeeded(it)
        }
        "${op.getSqlExpression()} ( ${quotedVals.join(', ')} ) "

    }


    private static String spParamsClause(Sql db, Map params) {
        List tags = []
        params.each {
            //Important: We OMIT null values
            if (it.value != null) {
                tags << "${it.key} = ${quoteAsNeeded(db, it.value)}"
            }
        }
        tags.join(", ")
    }

    /**
     *
     * @param db
     * @param spName
     * @param bean
     * @param spMapper it is essential that mapBack closures are properly defined for each field. spMapper maps bean keys ('names') to StoredProc params ('from's')
     * @return
     */
    public static Map executeSp(Sql db, String spName, Object bean, Mapper spMapper) {
        Map row = spMapper.mapBack(bean)

        db.firstRow("exec ${spName} ${spParamsClause(db, row)}")
    }

    public static Map executeSpWithParams(Sql db, String spName, Map params, Mapper spMapper) {
        Map row = spMapper.mapBack(params)   // :-) Actually, mapBack work with both POGOs and maps. So, actually, one could use executeSp instead

        db.firstRow("exec ${spName} ${spParamsClause(db, row)}")
    }

    /**
     *
     * @param db
     * @param table
     * @param mapper it is essential that mapBack closures are properly defined for each field. spMapper maps bean keys ('names') to StoredProc params ('from's')
     * @param fieldName
     * @param value
     * @return
     */

    public static Map simpleSelectFirstRowAsMap(Sql db, String table, SingleDbTableMapper mapper, String fieldName, def value) {
        def columnName = mapper.getSourceName(fieldName)
        def columnVal = mapper.transformBackFieldValue(fieldName, value)
        BaseFunctionalTest.logger.trace("column name is ${columnName}, value is: ${columnVal}")
        db.firstRow("select " + mapper.allFieldsListClause + " from ${table} where \"${columnName}\" = ${quoteAsNeeded(db, columnVal)}")
    }

    public static List<Map> simpleSelectAsListOfMaps(Sql db, String table, SingleDbTableMapper mapper, String fieldName, def value) {
        def columnName = mapper.getSourceName(fieldName)
        def columnVal = mapper.transformBackFieldValue(fieldName, value)
        BaseFunctionalTest.logger.trace("column name is ${columnName}, value is: ${columnVal}")
        List res = []
        db.eachRow("select " + mapper.allFieldsListClause + " from ${table} where \"${columnName}\" = ${quoteAsNeeded(db, columnVal)}") { row ->
            res << row.toRowResult().asImmutable()
        }
        res
    }

    public static List<Object> simpleSelectAsListOfKeyFiedValues(Sql db, String table, String keyField, SingleDbTableMapper mapper, String fieldName, def value) {
        def columnName = mapper.getSourceName(fieldName)
        def columnVal = mapper.transformBackFieldValue(fieldName, value)
        BaseFunctionalTest.logger.trace("column name is ${columnName}, value is: ${columnVal}")
        List res = []
        db.eachRow("select \"" + mapper.getSourceName(keyField) + "\" from \"" + table + "\" where \"" + columnName + "\" = ${columnVal}") { row ->
            res << mapper.getField(keyField).transform(mapper.getField(keyField), row.toRowResult() as Map)
        }
        res
    }


    public static <T> T updateObjectFromDb(Sql db, String table, SingleDbTableMapper mapper, T bean, String fieldName, def value) {
        def ret = simpleSelectFirstRowAsMap(db, table, mapper, fieldName, value)
        if (!ret) throw new NoSuchElementException("Nothing was found for your query (${bean.class}, ${fieldName} = ${value})")
        mapper.updateFrom(
                ret,
                bean
        )
    }

    static Object toCompatible(Object value) {
        if (value instanceof Date) return new java.sql.Date((value as Date).getTime())

        return value
    }
}